package com.atguigu.suger.mapper;

import com.atguigu.suger.bean.C3;
import com.atguigu.suger.bean.Spu;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

public interface ProductMapper {
    // 当只有一行一列的时候, 才能写具体的值.
    @Select("SELECT sum(order_amount) AS order_amount\n" +
        "FROM product_stats_2022\n" +
        "WHERE toYYYYMMDD(stt) = #{date}\n")
    BigDecimal gmv(@Param("date") int date);  // 20220215
    
    /*
    ┌─tm_name───┬─order_amount─┐
    │ 小米      │     26495.00 │
    │ 苹果      │     65576.00 │
    │ Redmi     │     14487.00 │
    │ TCL       │         0.00 │
    │ 华为      │     37270.00 │
    │ 香奈儿    │      1464.00 │
    │ 索芙特    │      1419.00 │
    │ 长粒香    │       158.00 │
    │ CAREMiLLE │       414.00 │
    │ 金沙河    │       206.00 │
    └───────────┴──────────────┘
    
    map 的key是列名 value就是列的值
     */
    @Select("SELECT\n" +
        "    tm_name,\n" +
        "    sum(order_amount) AS order_amount\n" +
        "FROM product_stats_2022\n" +
        "WHERE toYYYYMMDD(stt) = #{date}\n" +
        "GROUP BY tm_name\n")
    List<Map<String, Object>> gmvByTm(@Param("date") int date);
    
    /*
    ┌─category3_name─┬─order_amount─┐
    │ 唇部           │      1833.00 │
    │ 香水           │      1464.00 │
    │ 手机           │    129776.00 │
    │ 米面杂粮       │       364.00 │
    │ 平板电视       │     14052.00 │
    └────────────────┴──────────────┘

     */
    @Select("SELECT\n" +
        "    category3_name,\n" +
        "    sum(order_amount) AS order_amount\n" +
        "FROM product_stats_2022\n" +
        "WHERE toYYYYMMDD(stt) = #{date}\n" +
        "GROUP BY category3_name\n")
    List<C3> gmvByC3(@Param("date") int date);
    
    @Select("SELECT\n" +
        "    spu_name,\n" +
        "    sum(order_amount) AS order_amount\n" +
        "FROM product_stats_2022\n" +
        "WHERE toYYYYMMDD(stt) = #{date}\n" +
        "GROUP BY spu_name\n")
    List<Spu> gmvBySpu(@Param("date") int date);
    
}
